const express = require("express");
const { spawn, execSync } = require("child_process");
const path = require("path");
const fs = require("fs");
const initSqlJs = require("sql.js");

const app = express();
app.use(express.json());
const cors = require("cors");
app.use(cors());

const DEFAULT_PARAMS = {
  query: "宁波东方理工大学热点事件",
  queryNum: 20,
  sortTypeChoice: 1,
  noteType: 0,
  noteTime: 0,
  noteRange: 0,
  posDistance: 0,
};

const DB_PATH = path.join(__dirname, "datas", "sqlite_datas", "notes.db");

function findPythonCandidates() {
  const candidates = [
    process.env.PYTHON_BIN,
    process.env.PYTHON,
    process.env.PYTHON_PATH,
  ].filter(Boolean);

  // 在 Windows 上，使用 where.exe 查找 Python 的完整路径
  if (process.platform === "win32") {
    try {
      // 查找 python.exe 的完整路径
      const output = execSync("where.exe python", {
        encoding: "utf8",
        stdio: "pipe",
      });
      const pythonPaths = output
        .trim()
        .split(/\r?\n/)
        .map((p) => p.trim())
        .filter((p) => p.length > 0 && fs.existsSync(p));
      
      // 将找到的完整路径添加到候选列表的前面（优先级更高）
      candidates.unshift(...pythonPaths);

      // 也尝试查找 python3
      try {
        const python3Output = execSync("where.exe python3", {
          encoding: "utf8",
          stdio: "pipe",
        });
        const python3Paths = python3Output
          .trim()
          .split(/\r?\n/)
          .map((p) => p.trim())
          .filter((p) => p.length > 0 && fs.existsSync(p));
        candidates.unshift(...python3Paths);
      } catch (err) {
        // python3 不存在时忽略错误
      }
    } catch (err) {
      // where.exe 命令失败时，继续使用默认候选列表
    }
  }

  // 添加默认的命令名称（作为后备选项）
  candidates.push("py", "py.exe", "python3", "python");

  // 移除重复项，保留顺序
  const seen = new Set();
  return candidates.filter((candidate) => {
    if (!candidate || seen.has(candidate)) {
      return false;
    }
    seen.add(candidate);
    return true;
  });
}

const PYTHON_CANDIDATES = findPythonCandidates();
const SQL_WASM_PATH = path.join(__dirname, "node_modules", "sql.js", "dist");
const sqlPromise = initSqlJs({
  locateFile: (file) => path.join(SQL_WASM_PATH, file),
});

const UNIVERSITY_DB_DIR = path.join(__dirname, "db");
const UNIVERSITY_DB_ALIASES = {
  "宁波东方理工大学": "宁波东方理工大学.db",
  "宁波东方理工大学.db": "宁波东方理工大学.db",
  ningbo: "宁波东方理工大学.db",
  "西湖大学": "西湖大学.db",
  "西湖大学.db": "西湖大学.db",
  xihu: "西湖大学.db",
  westlake: "西湖大学.db",
};

function resolveUniversityDbSelection(rawValue) {
  if (typeof rawValue !== "string") {
    return null;
  }
  const trimmed = rawValue.trim();
  if (!trimmed || /[/\\]/.test(trimmed) || trimmed.includes("..")) {
    return null;
  }

  const aliasMatch =
    UNIVERSITY_DB_ALIASES[trimmed] ||
    UNIVERSITY_DB_ALIASES[trimmed.toLowerCase()];
  const fileName = aliasMatch || (trimmed.endsWith(".db") ? trimmed : `${trimmed}.db`);
  const resolvedPath = path.resolve(UNIVERSITY_DB_DIR, fileName);
  const expectedPrefix = `${UNIVERSITY_DB_DIR}${path.sep}`;
  if (
    resolvedPath !== UNIVERSITY_DB_DIR &&
    !resolvedPath.startsWith(expectedPrefix)
  ) {
    return null;
  }
  if (!fs.existsSync(resolvedPath)) {
    return null;
  }
  return { fileName, absolutePath: resolvedPath };
}

function findDefaultUniversityDbSelection() {
  const preferredNames = [
    "宁波东方理工大学.db",
    "西湖大学.db",
    "��������������ѧ.db",
    "������ѧ.db",
  ];

  for (const name of preferredNames) {
    const selection = resolveUniversityDbSelection(name);
    if (selection) {
      return selection;
    }
  }

  if (!fs.existsSync(UNIVERSITY_DB_DIR)) {
    return null;
  }

  try {
    const candidates = fs
      .readdirSync(UNIVERSITY_DB_DIR)
      .filter((entry) => typeof entry === "string" && entry.toLowerCase().endsWith(".db"));
    for (const candidate of candidates) {
      const selection = resolveUniversityDbSelection(candidate);
      if (selection) {
        return selection;
      }
    }
  } catch (err) {
    console.warn(
      `[DB] Failed to enumerate university databases: ${err.message || err}`
    );
  }

  return null;
}

function formatPercentage(count, total) {
  if (!Number.isFinite(count) || !Number.isFinite(total) || total <= 0 || count <= 0) {
    return "0%";
  }
  const percent = (count / total) * 100;
  const formatted = percent.toFixed(2).replace(/\.?0+$/, "");
  return `${formatted}%`;
}

async function readTopTagsFromDb(dbPath, limit = 5) {
  const SQL = await sqlPromise;
  const buffer = fs.readFileSync(dbPath);
  const db = new SQL.Database(buffer);

  try {
    const tagCounts = new Map();
    let totalTags = 0;

    const normalizeTag = (value) => {
      if (typeof value === "string") {
        const trimmed = value.trim();
        return trimmed.length > 0 ? trimmed : null;
      }
      if (value && typeof value === "object") {
        if (typeof value.name === "string") return normalizeTag(value.name);
        if (typeof value.text === "string") return normalizeTag(value.text);
      }
      return null;
    };

    const stmt = db.prepare("SELECT tags FROM notes WHERE tags IS NOT NULL AND TRIM(tags) <> ''");
    try {
      while (stmt.step()) {
        const row = stmt.getAsObject();
        if (typeof row.tags !== "string" || row.tags.trim().length === 0) continue;

        let parsed;
        try {
          parsed = JSON.parse(row.tags);
        } catch {
          parsed = row.tags;
        }

        if (Array.isArray(parsed)) {
          for (const tag of parsed) {
            const normalized = normalizeTag(tag);
            if (!normalized) continue;
            tagCounts.set(normalized, (tagCounts.get(normalized) || 0) + 1);
            totalTags++;
          }
        } else {
          const normalized = normalizeTag(parsed);
          if (!normalized) continue;
          tagCounts.set(normalized, (tagCounts.get(normalized) || 0) + 1);
          totalTags++;
        }
      }
    } finally {
      stmt.free();
    }

    const sorted = Array.from(tagCounts.entries()).sort((a, b) => b[1] - a[1]);
    const effectiveLimit = Math.min(limit > 0 ? limit : 5, 50);

    const keywords = sorted.slice(0, effectiveLimit).map(([keyword, count]) => ({
      keyword,
      count,  // ✅ 新增：直接把数量返回
      percentage: formatPercentage(count, totalTags),
    }));

    return { total: totalTags, keywords };
  } finally {
    db.close();
  }
}


async function readMonthlyCountsFromDb(dbPath, targetYear = new Date().getFullYear()) {
  const SQL = await sqlPromise;
  const buffer = fs.readFileSync(dbPath);
  const db = new SQL.Database(buffer);
  const counts = new Array(12).fill(0);
  const yearString = String(targetYear);

  try {
    const stmt = db.prepare(`
      SELECT CAST(strftime('%m', upload_time) AS INTEGER) AS month, COUNT(*) AS total
      FROM notes
      WHERE upload_time IS NOT NULL
        AND LENGTH(TRIM(upload_time)) > 0
        AND strftime('%Y', upload_time) = ?
      GROUP BY month
    `);
    stmt.bind([yearString]);
    while (stmt.step()) {
      const row = stmt.getAsObject();
      const monthNumber = Number.parseInt(row.month, 10);
      if (Number.isFinite(monthNumber) && monthNumber >= 1 && monthNumber <= 12) {
        counts[monthNumber - 1] = row.total;
      }
    }
    stmt.free();
  } finally {
    db.close();
  }

  return {
    year: targetYear,
    monthly: counts.map((total, index) => ({
      month: index + 1,
      count: total,
    })),
  };
}

function usesPyLauncher(pythonBinary) {
  const name = path.basename(pythonBinary).toLowerCase();
  return name === "py" || name === "py.exe";
}

function buildPythonArgs(pythonBinary, params) {
  const scriptPath = path.join(__dirname, "scripts", "run_spider.py");
  const baseArgs = usesPyLauncher(pythonBinary)
    ? ["-3", scriptPath]
    : [scriptPath];
  const args = [
    ...baseArgs,
    "--query",
    params.query,
    "--query-num",
    String(params.queryNum),
    "--sort-type-choice",
    String(
      params.sortTypeChoice ??
        params.sortType ??
        params.sort_type_choice ??
        0
    ),
    "--note-type",
    String(params.noteType),
    "--note-time",
    String(params.noteTime),
    "--note-range",
    String(params.noteRange),
    "--pos-distance",
    String(params.posDistance),
    "--save-choice",
    "all",
  ];
  if (params.geo) {
    args.push("--geo", JSON.stringify(params.geo));
  }
  return args;
}

async function runSpider(params) {
  const attempts = PYTHON_CANDIDATES.length > 0 ? PYTHON_CANDIDATES : ["py"];
  let lastError = null;

  for (const pythonBinary of attempts) {
    try {
      return await runSpiderWithBinary(pythonBinary, params);
    } catch (err) {
      lastError = err;
      if (err.code === "ENOENT") {
        continue;
      }
      break;
    }
  }

  const attemptedList = attempts.join(", ");
  const message = lastError
    ? `${lastError.message} (attempted: ${attemptedList})`
    : `Unable to locate a Python interpreter (attempted: ${attemptedList})`;
  throw new Error(message);
}

function runSpiderWithBinary(pythonBinary, params) {
  return new Promise((resolve, reject) => {
    const args = buildPythonArgs(pythonBinary, params);
    const child = spawn(pythonBinary, args, { cwd: __dirname });
    let stdout = "";
    let stderr = "";

    child.stdout.on("data", (chunk) => {
      stdout += chunk.toString("utf8");
    });
    child.stderr.on("data", (chunk) => {
      stderr += chunk.toString("utf8");
    });
    child.on("error", (err) => {
      err.code = err.code || "UNKNOWN";
      reject(err);
    });
    child.on("close", (code) => {
      // Windows 错误代码 9009 表示"命令或程序名未找到"
      if (code === 9009) {
        const err = new Error(`Python interpreter not found: ${pythonBinary}`);
        err.code = "ENOENT";
        err.exitCode = 9009;
        return reject(err);
      }
      if (code !== 0) {
        return reject(
          new Error(
            `Spider exited with code ${code}: ${stderr.trim() || stdout.trim()}`
          )
        );
      }
      const lines = stdout.trim().split(/\r?\n/).filter(Boolean);
      const lastLine = lines.pop() || "{}";
      try {
        const payload = JSON.parse(lastLine);
        resolve(payload);
      } catch (err) {
        reject(new Error(`Failed to parse spider output: ${err.message}`));
      }
    });
  });
}

async function readNotes(options = {}) {
  const { noteUrls, searchTerm, limit } = options;
  if (!fs.existsSync(DB_PATH)) {
    return [];
  }
  const SQL = await sqlPromise;
  const fileBuffer = fs.readFileSync(DB_PATH);
  const db = new SQL.Database(fileBuffer);
  let rows = [];

  try {
    if (Array.isArray(noteUrls) && noteUrls.length > 0) {
      const placeholders = noteUrls.map(() => "?").join(",");
      const stmt = db.prepare(
        `SELECT * FROM notes WHERE note_url IN (${placeholders}) ORDER BY COALESCE(fetched_at, upload_time) DESC`
      );
      stmt.bind(noteUrls);
      while (stmt.step()) {
        rows.push(stmt.getAsObject());
      }
      stmt.free();
    } else {
      const filters = [];
      const bindValues = [];
      const trimmedSearch =
        typeof searchTerm === "string" ? searchTerm.trim() : "";
      if (trimmedSearch.length > 0) {
        filters.push("(title LIKE ? OR note_desc LIKE ?)");
        const pattern = `%${trimmedSearch}%`;
        bindValues.push(pattern, pattern);
      }
      let sql = "SELECT * FROM notes";
      if (filters.length > 0) {
        sql += ` WHERE ${filters.join(" AND ")}`;
      }
      sql += " ORDER BY COALESCE(fetched_at, upload_time) DESC";
      const effectiveLimit =
        Number.isFinite(limit) && limit > 0
          ? Math.min(Math.floor(limit), 500)
          : 50;
      sql += " LIMIT ?";
      bindValues.push(effectiveLimit);

      const stmt = db.prepare(sql);
      stmt.bind(bindValues);
      while (stmt.step()) {
        rows.push(stmt.getAsObject());
      }
      stmt.free();
    }
  } finally {
    db.close();
  }

  return rows.map(formatRow);
}

async function saveNotesToDb(notes) {
  if (!Array.isArray(notes) || notes.length === 0) {
    console.log("[DB] No new notes to save.");
    return;
  }

  const SQL = await sqlPromise;

  // 确保数据库目录存在
  const dbDir = path.dirname(DB_PATH);
  if (!fs.existsSync(dbDir)) fs.mkdirSync(dbDir, { recursive: true });

  // 如果数据库文件不存在，则初始化空库
  let db;
  if (fs.existsSync(DB_PATH)) {
    const fileBuffer = fs.readFileSync(DB_PATH);
    db = new SQL.Database(fileBuffer);
  } else {
    db = new SQL.Database();
    db.run(`
      CREATE TABLE IF NOT EXISTS notes (
        note_id TEXT PRIMARY KEY,
        note_url TEXT,
        note_type TEXT,
        user_id TEXT,
        home_url TEXT,
        nickname TEXT,
        avatar TEXT,
        title TEXT,
        note_desc TEXT,
        liked_count INTEGER,
        collected_count INTEGER,
        comment_count INTEGER,
        share_count INTEGER,
        video_cover TEXT,
        video_addr TEXT,
        image_list TEXT,
        tags TEXT,
        upload_time TEXT,
        ip_location TEXT,
        raw_json TEXT
      );
    `);
  }

  const insertStmt = db.prepare(`
    INSERT OR REPLACE INTO notes (
      note_id, note_url, note_type, user_id, home_url, nickname, avatar,
      title, note_desc, liked_count, collected_count, comment_count, share_count,
      video_cover, video_addr, image_list, tags, upload_time, ip_location, raw_json
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  let count = 0;
  for (const note of notes) {
    try {
      insertStmt.run([
        note.note_id,
        note.note_url,
        note.note_type,
        note.user_id,
        note.home_url,
        note.nickname,
        note.avatar,
        note.title,
        note.desc,
        note.liked_count,
        note.collected_count,
        note.comment_count,
        note.share_count,
        note.video_cover,
        note.video_addr,
        JSON.stringify(note.image_list || []),
        JSON.stringify(note.tags || []),
        note.upload_time,
        note.ip_location,
        JSON.stringify(note.raw || {}),
      ]);
      count++;
    } catch (err) {
      console.error(`[DB] Failed to insert note ${note.note_id}:`, err.message);
    }
  }

  insertStmt.free();

  // 将内存数据库写回文件
  const data = Buffer.from(db.export());
  fs.writeFileSync(DB_PATH, data);
  db.close();
  console.log(`[DB] Successfully saved ${count} notes to database.`);
}

function formatRow(row) {
  const parseJsonField = (value, fallback) => {
    if (typeof value !== "string" || value.length === 0) {
      return fallback;
    }
    try {
      return JSON.parse(value);
    } catch (err) {
      return fallback;
    }
  };

  return {
    note_id: row.note_id,
    note_url: row.note_url,
    note_type: row.note_type,
    user_id: row.user_id,
    home_url: row.home_url,
    nickname: row.nickname,
    avatar: row.avatar,
    title: row.title,
    desc: row.note_desc,
    liked_count: row.liked_count,
    collected_count: row.collected_count,
    comment_count: row.comment_count,
    share_count: row.share_count,
    video_cover: row.video_cover,
    video_addr: row.video_addr,
    image_list: parseJsonField(row.image_list, []),
    tags: parseJsonField(row.tags, []),
    upload_time: row.upload_time,
    fetched_at: row.fetched_at || null,
    ip_location: row.ip_location,
    raw: parseJsonField(row.raw_json, {}),
  };
}

function parseGeo(query) {
  if (!query) {
    return undefined;
  }
  try {
    return JSON.parse(query);
  } catch (err) {
    throw new Error("Invalid geo JSON payload");
  }
}

function parseNoteUrlsParam(value) {
  if (!value) {
    return undefined;
  }
  const normalize = (items) =>
    items
      .map((item) => (typeof item === "string" ? item.trim() : ""))
      .filter((item) => item.length > 0);

  if (Array.isArray(value)) {
    return normalize(value);
  }
  if (typeof value === "string") {
    try {
      const parsed = JSON.parse(value);
      if (Array.isArray(parsed)) {
        return normalize(parsed);
      }
    } catch (err) {
      // fall through to comma separated handling
    }
    return normalize(value.split(","));
  }
  return undefined;
}

function msUntilNextMidnight() {
  console.log("开始执行");
  const now = new Date();
  const next = new Date(now);
  next.setHours(24, 0, 0, 0);
  return next.getTime() - now.getTime();
}

// function msUntilNextMidnight() {
//   console.log("开始执行");
//   const now = new Date();
//   const next = new Date(now.getTime() + 10 * 1000); // 当前时间 + 60 秒
//   return next.getTime() - now.getTime(); // 始终保持 60000 毫秒
// }

async function runDailySpiderJob() {
  console.log(
    `[Scheduler] Triggering spider run at ${new Date().toISOString()}`
  );
  try {
    const result = await runSpider({ ...DEFAULT_PARAMS });
    if (!result.success) {
      console.warn(
        `[Scheduler] Spider reported failure: ${
          result.message || "unknown error"
        }`
      );
    } else {
      const urlCount = Array.isArray(result.note_urls)
        ? result.note_urls.length
        : 0;
      console.log(
        `[Scheduler] Spider run completed successfully. Processed ${urlCount} urls.`
      );
    }
  } catch (err) {
    // 检查是否是 Python 未找到的错误
    if (err.message && err.message.includes("Unable to locate a Python interpreter")) {
      console.warn(
        `[Scheduler] Python interpreter not found. Please ensure Python is installed and available in PATH. Error: ${err.message}`
      );
    } else {
      console.error("[Scheduler] Spider run failed:", err.message || err);
    }
  }
}

function scheduleDailySpiderRun() {
  const scheduleNext = () => {
    let delay = msUntilNextMidnight();
    if (delay < 1000) {
      delay = 0;
    }
    setTimeout(async () => {
      await runDailySpiderJob();
      scheduleNext();
    }, delay);
  };
  scheduleNext();
}

async function monthlyCountsHandler(req, res) {
  try {
    const queryDb = Array.isArray(req.query.db)
      ? req.query.db[0]
      : req.query.db;
    const queryYearRaw = Array.isArray(req.query.year)
      ? req.query.year[0]
      : req.query.year;
    const bodyYearRaw =
      typeof req.body?.year === "number" || typeof req.body?.year === "string"
        ? req.body.year
        : undefined;

    const parsedYear = Number.parseInt(
      (bodyYearRaw ?? queryYearRaw ?? "").toString(),
      10
    );
    const currentYear = new Date().getFullYear();
    const targetYear = Number.isFinite(parsedYear) ? parsedYear : currentYear;

    const dbSelection =
      resolveUniversityDbSelection(queryDb) ||
      resolveUniversityDbSelection(
        typeof req.body?.db === "string" ? req.body.db : undefined
      );

    if (!dbSelection) {
      return res.status(400).json({
        success: false,
        message: "Invalid or missing db parameter",
      });
    }

    const monthlyCounts = await readMonthlyCountsFromDb(
      dbSelection.absolutePath,
      targetYear
    );
    return res.json({
      success: true,
      message: "",
      db: dbSelection.fileName,
      data: monthlyCounts,
    });
  } catch (err) {
    console.error("[DB] Failed to read monthly counts:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to load monthly statistics",
    });
  }
}

app.get("/notes/monthly-counts", monthlyCountsHandler);
app.post("/notes/monthly-counts", monthlyCountsHandler);

async function topTagsHandler(req, res) {
  try {
    // 1️⃣ 解析数据库参数
    const queryDb = Array.isArray(req.query.db) ? req.query.db[0] : req.query.db;
    const bodyDb = typeof req.body?.db === "string" ? req.body.db : undefined;

    // 2️⃣ 解析 limit 参数
    const queryLimitRaw = Array.isArray(req.query.limit)
      ? req.query.limit[0]
      : req.query.limit;
    const bodyLimitRaw =
      typeof req.body?.limit === "number" || typeof req.body?.limit === "string"
        ? req.body.limit
        : undefined;
    const parsedLimit = Number.parseInt(
      (bodyLimitRaw ?? queryLimitRaw ?? "").toString(),
      10
    );
    const limit =
      Number.isFinite(parsedLimit) && parsedLimit > 0
        ? Math.min(parsedLimit, 50)
        : 5;

    // 3️⃣ 确定数据库文件路径
    const dbSelection =
      resolveUniversityDbSelection(queryDb) ||
      resolveUniversityDbSelection(bodyDb) ||
      findDefaultUniversityDbSelection();

    if (!dbSelection) {
      return res.status(404).json({
        success: false,
        message: "Database not found",
      });
    }

    // 4️⃣ 调用 readTopTagsFromDb 读取统计结果
    const { keywords, total } = await readTopTagsFromDb(
      dbSelection.absolutePath,
      limit
    );

    // 5️⃣ 打印每个关键词的出现次数和百分比
    console.log(`\n[TopTags] 数据库：${dbSelection.fileName}`);
    console.log(`[TopTags] 总标签数：${total}`);
    console.log(`[TopTags] 前 ${keywords.length} 个关键词：`);
    keywords.forEach((k, idx) => {
      console.log(
        `  ${idx + 1}. ${k.keyword} — ${k.count} 次，占比 ${k.percentage}`
      );
    });

    // 6️⃣ 返回结果给前端（包含 count）
    return res.json({
      success: true,
      message: "",
      db: dbSelection.fileName,
      total,
      data: keywords.map((k) => ({
        keyword: k.keyword,
        count: k.count,
        percentage: k.percentage,
      })),
    });
  } catch (err) {
    console.error("[DB] Failed to compute top tags:", err);
    return res.status(500).json({
      success: false,
      message: "Failed to compute top tags",
    });
  }
}


app.get("/notes/top-tags", topTagsHandler);
app.post("/notes/top-tags", topTagsHandler);

app.get("/notes", async (req, res) => {
  try {
    const noteUrls = parseNoteUrlsParam(req.query.noteUrls);
    let searchTerm = req.query.query;
    try {
      parseGeo(req.query.geo);
    } catch (err) {
      return res.status(400).json({ success: false, message: err.message });
    }

    if (Array.isArray(noteUrls) && noteUrls.length > 0) {
      searchTerm = undefined;
    }

    const queryLimit = Number.parseInt(req.query.queryNum, 10);
    const notes = await readNotes({
      noteUrls,
      searchTerm,
      limit: Number.isFinite(queryLimit) ? queryLimit : undefined,
    });
    return res.json({
      success: true,
      message: "",
      count: notes.length,
      data: notes,
    });
  } catch (err) {
    return res.status(500).json({
      success: false,
      message: err.message,
    });
  }
});

app.get("/health", (req, res) => {
  res.json({ ok: true });
});

const PORT = Number.parseInt(process.env.PORT, 10) || 3000;
app.listen(PORT, () => {
  console.log(`Server listening on port ${PORT}`);
  scheduleDailySpiderRun();
});
